

-- 系统配置表
DROP TABLE IF EXISTS `yl_system_config`;
CREATE TABLE `yl_system_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` varchar(64) NOT NULL COMMENT '配置键',
  `value` text COMMENT '配置值',
  `title` varchar(128) DEFAULT '' COMMENT '配置标题',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

INSERT INTO `yl_system_config` (`key`, `value`, `title`) VALUES
('system_name', 'YouFaceID实名分发系统', '系统名称'),
('system_notice', '欢迎使用YouFaceID实名分发系统，请遵守平台使用规范。', '系统公告'),
('icp_number', '苏ICP备XXXXXXXX号', '备案号'),
('contact_phone', '400-000-0000', '联系电话'),
('copyright', 'Copyright © 2026 YouFaceID实名分发系统 All Rights Reserved.', '版权信息'),
('logo_url', '', 'Logo地址'),
('home_slogan', '安全、稳定、高效的实名认证服务', '首页标语'),
('api_doc_url', '', 'API文档链接');

-- 管理员表
DROP TABLE IF EXISTS `yl_admin`;
CREATE TABLE `yl_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL COMMENT '账号',
  `password` varchar(128) NOT NULL COMMENT '密码',
  `nickname` varchar(64) DEFAULT '' COMMENT '昵称',
  `phone` varchar(20) DEFAULT '' COMMENT '手机号',
  `email` varchar(128) DEFAULT '' COMMENT '邮箱',
  `role` varchar(32) DEFAULT 'admin' COMMENT '角色',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1正常0禁用',
  `login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `login_ip` varchar(64) DEFAULT '' COMMENT '最后登录IP',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

INSERT INTO `yl_admin` (`username`, `password`, `nickname`, `role`) VALUES
('admin', '$2y$10$8xVNqGqGRsBqRJXjKqmG6eQZvRwO4DVfBBHZpP2CMwWqGAcVqMfUG', '超级管理员', 'super');

-- 邮箱配置表
DROP TABLE IF EXISTS `yl_email_config`;
CREATE TABLE `yl_email_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `smtp_host` varchar(128) DEFAULT '' COMMENT 'SMTP服务器',
  `smtp_port` int(11) DEFAULT 465 COMMENT '端口',
  `smtp_user` varchar(128) DEFAULT '' COMMENT '邮箱账号',
  `smtp_pass` varchar(256) DEFAULT '' COMMENT '授权码',
  `sender_name` varchar(64) DEFAULT '' COMMENT '发件人名称',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邮箱配置表';

-- 通道表(上游实名认证服务商)
DROP TABLE IF EXISTS `yl_channel`;
CREATE TABLE `yl_channel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL COMMENT '通道名称',
  `type` varchar(64) NOT NULL COMMENT '通道类型:idcard_2-身份证二要素,face-人脸识别,phone_3-运营商三要素',
  `type_name` varchar(64) DEFAULT '' COMMENT '类型显示名',
  `api_url` varchar(256) DEFAULT '' COMMENT 'API接口地址',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1开启0关闭',
  `sort` int(11) DEFAULT 100 COMMENT '排序',
  `remark` varchar(512) DEFAULT '' COMMENT '备注',
  `need_photo` tinyint(1) DEFAULT 0 COMMENT '是否需要上传照片:1是0否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通道表';

-- 通道参数表(键值对存储对接参数)
DROP TABLE IF EXISTS `yl_channel_param`;
CREATE TABLE `yl_channel_param` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) NOT NULL COMMENT '通道ID',
  `param_key` varchar(128) NOT NULL COMMENT '参数键',
  `param_value` text COMMENT '参数值',
  `param_title` varchar(128) DEFAULT '' COMMENT '参数说明',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_channel_id` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通道参数表';

-- 套餐表
DROP TABLE IF EXISTS `yl_package`;
CREATE TABLE `yl_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL COMMENT '套餐名称',
  `channel_id` int(11) NOT NULL COMMENT '关联通道ID',
  `call_limit` int(11) NOT NULL DEFAULT 0 COMMENT '调用次数限制',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '价格',
  `valid_days` int(11) NOT NULL DEFAULT 30 COMMENT '有效期(天)',
  `sort` int(11) DEFAULT 100 COMMENT '排序权重',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1上架0下架',
  `description` text COMMENT '套餐描述',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_channel_id` (`channel_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='套餐表';

-- 用户表
DROP TABLE IF EXISTS `yl_user`;
CREATE TABLE `yl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(128) NOT NULL COMMENT '密码',
  `nickname` varchar(64) DEFAULT '' COMMENT '昵称',
  `phone` varchar(20) DEFAULT '' COMMENT '手机号',
  `email` varchar(128) DEFAULT '' COMMENT '邮箱',
  `avatar` varchar(256) DEFAULT '' COMMENT '头像',
  `balance` decimal(10,2) DEFAULT 0.00 COMMENT '账户余额',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1正常0禁用',
  `register_ip` varchar(64) DEFAULT '' COMMENT '注册IP',
  `login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `login_ip` varchar(64) DEFAULT '' COMMENT '最后登录IP',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_phone` (`phone`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 用户应用表(AppID/AppKey接入凭证)
DROP TABLE IF EXISTS `yl_user_app`;
CREATE TABLE `yl_user_app` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '所属用户ID',
  `name` varchar(128) NOT NULL COMMENT '应用名称',
  `app_id` varchar(64) NOT NULL COMMENT 'AppID',
  `app_key` varchar(64) NOT NULL COMMENT 'AppKey',
  `remain_calls` int(11) DEFAULT 0 COMMENT '剩余调用次数',
  `expire_time` datetime DEFAULT NULL COMMENT '到期时间',
  `package_id` int(11) DEFAULT 0 COMMENT '当前套餐ID',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1启用0禁用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_app_id` (`app_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户应用表';

-- 订单表
DROP TABLE IF EXISTS `yl_order`;
CREATE TABLE `yl_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `package_id` int(11) NOT NULL COMMENT '套餐ID',
  `app_id` int(11) DEFAULT 0 COMMENT '关联应用ID',
  `package_name` varchar(128) DEFAULT '' COMMENT '套餐名称(冗余)',
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '支付金额',
  `pay_status` tinyint(1) DEFAULT 0 COMMENT '支付状态:0未支付1已支付2已失效',
  `pay_type` varchar(32) DEFAULT 'offline' COMMENT '支付方式:alipay-支付宝,wechat-微信,offline-线下转账',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `expire_time` datetime DEFAULT NULL COMMENT '订单过期时间',
  `remark` varchar(512) DEFAULT '' COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_pay_status` (`pay_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- API调用日志表
DROP TABLE IF EXISTS `yl_api_log`;
CREATE TABLE `yl_api_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `app_id` varchar(64) DEFAULT '' COMMENT 'AppID',
  `api_name` varchar(64) DEFAULT '' COMMENT '接口名称',
  `api_title` varchar(128) DEFAULT '' COMMENT '接口显示名',
  `channel_id` int(11) DEFAULT 0 COMMENT '使用的通道ID',
  `request_params` text COMMENT '请求参数(脱敏)',
  `response_result` text COMMENT '返回结果',
  `status` tinyint(1) DEFAULT 1 COMMENT '调用结果:1成功0失败',
  `cost_time` decimal(10,3) DEFAULT 0.000 COMMENT '耗时(秒)',
  `ip` varchar(64) DEFAULT '' COMMENT '调用IP',
  `err_msg` varchar(512) DEFAULT '' COMMENT '错误信息',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_app_id` (`app_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_api_name` (`api_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API调用日志表';

-- 管理员操作日志表
DROP TABLE IF EXISTS `yl_admin_log`;
CREATE TABLE `yl_admin_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) NOT NULL COMMENT '管理员ID',
  `admin_name` varchar(64) DEFAULT '' COMMENT '管理员账号',
  `action` varchar(128) DEFAULT '' COMMENT '操作动作',
  `content` text COMMENT '操作内容',
  `ip` varchar(64) DEFAULT '' COMMENT '操作IP',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员操作日志表';

-- 充值记录表
DROP TABLE IF EXISTS `yl_recharge_record`;
CREATE TABLE `yl_recharge_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  `type` varchar(32) DEFAULT 'manual' COMMENT '类型:manual-手动充值,order-消费充值',
  `operator_id` int(11) DEFAULT 0 COMMENT '操作人ID(管理员)',
  `remark` varchar(512) DEFAULT '' COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值记录表';

-- 消费记录表
DROP TABLE IF EXISTS `yl_consume_record`;
CREATE TABLE `yl_consume_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `app_id` varchar(64) DEFAULT '' COMMENT '应用ID',
  `consume_count` int(11) DEFAULT 1 COMMENT '消费次数',
  `api_name` varchar(64) DEFAULT '' COMMENT '接口名称',
  `remark` varchar(256) DEFAULT '' COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_app_id` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消费记录表';

-- 工单表
DROP TABLE IF EXISTS `yl_ticket`;
CREATE TABLE `yl_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `title` varchar(256) NOT NULL COMMENT '工单标题',
  `content` text COMMENT '工单内容',
  `reply` text COMMENT '回复内容',
  `status` tinyint(1) DEFAULT 0 COMMENT '状态:0待处理1已回复2已关闭',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单表';

-- 支付通道表
DROP TABLE IF EXISTS `yl_pay_channel`;
CREATE TABLE `yl_pay_channel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL COMMENT '支付通道名称',
  `version` varchar(16) DEFAULT 'v2' COMMENT '接口版本:v1或v2',
  `api_url` varchar(256) DEFAULT '' COMMENT '支付网关地址',
  `merchant_id` varchar(64) DEFAULT '' COMMENT '商户ID',
  `api_key` varchar(512) DEFAULT '' COMMENT '商户密钥(加密存储)',
  `pay_type` varchar(32) DEFAULT 'alipay' COMMENT '默认支付方式',
  `status` tinyint(1) DEFAULT 1 COMMENT '状态:1开启0关闭',
  `sort` int(11) DEFAULT 100 COMMENT '排序',
  `remark` varchar(512) DEFAULT '' COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付通道表';
